
-- ================================================================      
-- Author:   <Ei Myat Mon>      
-- Role:   <Programmer>      
-- Create date:  <October.03.2014>      
-- Description:  <sp_cr_AgentLedger_Rpt>      
-- Procedure Name: <[dbo].[sp_cr_AgentLedger_Rpt]>      
-- Return Type:  <Table>      
-- ================================================================      
-- UPDATED HISTORY      
-- ================================================================      
-- Name:
-- Date:   
-- Description:   
      
-- Name:  <>      
-- Date:  <>      
-- Description: <>  
    
-- Name:  <>      
-- Date:  <>      
-- Description: <>      
-- ================================================================  

CREATE PROCEDURE [dbo].[sp_cr_AgentLedger_Rpt]
	@fromdate AS VARCHAR(10),
	@todate AS VARCHAR(10),
	@fromagentid AS NVARCHAR(100),
	@toagentid AS NVARCHAR(100),
	@fromtypeid AS NVARCHAR(100),
	@totypeid AS NVARCHAR(100),
	@currencyid AS NVARCHAR(100),
	@checkcur AS BIT,
	@branchid bigint
AS

BEGIN

	DECLARE @sql NVARCHAR(MAX)
	DECLARE @sqlOpening NVARCHAR(MAX)
	DECLARE @temp VARCHAR(100)
	DECLARE @colagent NVARCHAR(MAX)
	DECLARE @coltype NVARCHAR(MAX)
	DECLARE @colcurrency NVARCHAR(MAX)

	IF(@fromdate > @todate)
	BEGIN
		SET @temp = @fromdate
		SET @fromdate = @todate
		SET @todate = @temp
	END
	--

	--IF(@fromdate = '')SET @fromdate = '2014-10-01'
	--IF(@todate = '')SET @todate = '20140731'
	
	if(@fromagentid <> '' and @toagentid <> '' and @fromagentid > @toagentid)
	begin
		set @temp = @fromagentid
		set @fromagentid = @toagentid
		set @toagentid = @temp
	end

	if(@fromagentid = '' and @toagentid = '')
		set @colagent = ''
	
	else
	begin
		if(@fromagentid = '')set @fromagentid = (select top(1)agent_code from agent where status = 0 order by agent_code asc)
		if(@toagentid = '')set @toagentid = (select top(1)agent_code from agent where status = 0 order by agent_code desc)
		set @colagent = 'And(a.agent_code between N'''+@fromagentid+''' and N'''+@toagentid+''')'
	end

	if(@fromtypeid <> '' and @totypeid <> '' and @fromtypeid > @totypeid)
	begin
		set @temp = @fromtypeid
		set @fromtypeid = @totypeid
		set @totypeid = @temp
	end

	if(@fromtypeid = '' and @totypeid = '')
		set @coltype = ''
	
	else
	begin
		if(@fromtypeid = '')set @fromtypeid = (select top(1)type_code from type  order by type_code asc)
		if(@totypeid = '')set @totypeid = (select top(1)type_code from type order by type_code desc)
		set @coltype = 'And(t.type_code between N'''+@fromtypeid+''' and N'''+@totypeid+''')'
	end

	IF(@checkcur = 1)
		SET @colcurrency = 'AND cur.CurrencyCode = N'''+@currencyid+''''
	ELSE
		SET @colcurrency = ''

/*For Title*/
	declare @title nvarchar(MAX)

	if(@fromagentid = '')set @fromagentid = (select top(1)agent_code from agent where status = 0 and branchID = @branchid order by agent_code asc)
	if(@toagentid = '')set @toagentid = (select top(1)agent_code from agent where status = 0 and branchID = @branchid order by agent_code desc)
	if(@checkcur = 0)
		set @title = 'Agent Ledger Information From '''+''+@fromagentid+''' To '''+@toagentid+''' By Home Currency'
	else
		set @title = 'Agent Ledger Information From '''+''+@fromagentid+''' To '''+@toagentid+''' By '''+@currencyid+''''

	/*For Title*/

	DECLARE @startdate VARCHAR(8)

	SET @startdate = (select replace(FinancialYearStartDate,'-','') from SetUp)

	declare @month as varchar(2)
	DECLARE @ldate AS VARCHAR(8)
	SET @ldate = SUBSTRING(@fromdate, 5 ,4)
	set @month = SUBSTRING(@fromdate, 5 ,2)

	DECLARE @rdate AS VARCHAR(10)

	IF(@ldate = '0101' )
	BEGIN
		SET @rdate =  CAST (CAST (SUBSTRING(@fromdate, 1 ,4) AS INT ) - 1 AS VARCHAR(10))+ '1231'

	END
	else if(@ldate between '0102' and '1231' and @ldate <> @month+'01')
		SET @rdate = @fromdate - 1 

	else
	begin
		SET @rdate =  CAST (CAST (SUBSTRING(@fromdate, 1 ,6) AS INT ) - 1 AS VARCHAR(10))+ '31'
	end

	DECLARE @tblOpening AS TABLE
	(
		AgentID VARCHAR(100),
		AgentName VARCHAR(100),
		Currency_ID VARCHAR(100),
		Amount DECIMAL(38,8)
	)


	DECLARE @tbl1 AS TABLE
	(
		AgentID VARCHAR(100),
		AgentName VARCHAR(100),
		Currency_ID VARCHAR(100),
		Date varchar(10),
		Description VARCHAR(100),
		TransactionID VARCHAR(100),
		Amount DECIMAL(38,8)
	)
	DECLARE @tblAgent AS TABLE
	(
		No INT,
		AgentID VARCHAR(100)
	)

	DECLARE @tblAgentInfo AS TABLE
	(
		No INT,
		AgentID VARCHAR(100),
		AgentName VARCHAR(100),
		Date varchar(10),
		Description VARCHAR(100),
		TransactionID VARCHAR(100),
		Debit DECIMAL(38,8),
		Credit DECIMAL(38,8)
	)
	DECLARE @tbl2 AS TABLE
	(
		No INT,
		AgentID VARCHAR(100),
		AgentName VARCHAR(100),
		Date varchar(10),
		Description VARCHAR(100),
		TransactionID VARCHAR(100),
		Debit DECIMAL(38,8),
		Credit DECIMAL(38,8)
	)

	DECLARE @tblResult AS TABLE
	(
		No INT,
		AgentID VARCHAR(100),
		AgentName VARCHAR(100),
		Date varchar(10),
		Description VARCHAR(100),
		TransactionID VARCHAR(100),
		Debit DECIMAL(38,8),
		Credit DECIMAL(38,8),
		Balance DECIMAL(38,8)
	)
	DECLARE @tblFinalResult AS TABLE
	(
		No INT,
		Title nvarchar(200),
		AgentID VARCHAR(100),
		AgentName VARCHAR(100),
		Date varchar(10),
		Description VARCHAR(100),
		TransactionID VARCHAR(100),
		Debit DECIMAL(38,8),
		Credit DECIMAL(38,8),
		Balance DECIMAL(38,8)
	)

	if(@checkcur = 1)
	begin
		set @sqlOpening = 'select 
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				oa.amount Amount 
				from OpeningAgent oa
				left join Agent a on a.agent_id = oa.agent_Id
				left join Currency cur on cur.CurrencyId = oa.currency_Id
				left join type t on t.type_id = a.type_id
			where oa.branchid = '''+convert(varchar(MAX),@branchid)+''' and(replace(CONVERT(VARCHAR(10),CAST(openingagentDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				cp.PaymentAmount Amount
				from CashPayment cp
				left join Agent a on a.agent_id = cp.AgentID
				left join Currency cur on cur.CurrencyId = cp.CurrencyID
				left join type t on t.type_id = a.type_id
			where TRType = 1 and cp.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and (replace(CONVERT(VARCHAR(10),CAST(cp.TRDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				cr.TotalPaymentAmount Amount
				from CashRemittanceToAgent cr
				left join Agent a on a.agent_id = cr.ToAgentID
				left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
				left join type t on t.type_id = a.type_id
			Where 1=1 and (replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+') and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''''++@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				((cr.TotalReceivedAmount+cr.extrafees)-cr.received) Amount
				from CashRemittanceByAgent cr
				left join Agent a on a.agent_id = cr.FromAgentID
				left join Currency cur on cur.CurrencyId = cr.ReceivedCurrencyID
				left join type t on t.type_id = a.type_id
			where TransactionType = 1 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				cpr.Amount Amount
				from CashPaymentByReturn cpr
				left join Agent a on a.agent_id = cpr.AgentID
				left join Currency cur on cur.CurrencyId = cpr.CurrencyID
				left join type t on t.type_id = a.type_id
			where cpr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and(replace(CONVERT(VARCHAR(10),CAST(cpr.SlipDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				act.PaidAmount Amount
				from AgentCashTransaction act
				left join Agent a on a.agent_id = act.AgentID
				left join Currency cur on cur.CurrencyId = act.CurrencyID
				left join type t on t.type_id = a.type_id 
			where act.TransactionType = 1 and act.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				-dptd.Amount Amount
				from DefinePaymentTypeDetail dptd
				left join DefinePaymentType dp on dp.InvoiceID = dptd.InvoiceID
				left join Agent a on a.agent_id = dptd.AgentID
				left join Currency cur on cur.CurrencyId = dptd.CurrencyID
				left join type t on t.type_id = a.type_id
			where isnull(agent_id,0) <> 0 and dp.branchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(dp.InvoiceDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
			+@colagent+@coltype+@colcurrency+
			--union all
			--select
			--	*
			--	from CashReturn cr
			--	left join Agent a on a.agent_id = cr.AgentID
			--	left join Currency cur on cur.CurrencyId = cr.cur
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				-crd.PaymentAmount Amount
				from CashRemittanceByAgent cr
				left join CashRemittanceByAgentDetails crd on cr.ReceiptID = crd.ReceiptID
				left join Agent a on a.agent_id = crd.ToAgentID
				left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
				left join type t on t.type_id = a.type_id
			where crd.ToAgentID <> 0 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				-act.PaidAmount Amount
				from AgentCashTransaction act
				left join Agent a on a.agent_id = act.AgentID
				left join Currency cur on cur.CurrencyId = act.CurrencyID
				left join type t on t.type_id = a.type_id
			where TransactionType = 0 and act.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
			+@colagent+@coltype+@colcurrency

		INSERT INTO @tblOpening
		EXEC (@sqlOpening)
		--select (@sqlOpening)

	set @sql = 'select 
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(oa.openingagentDate AS DATE),103),''-'','''')Date,
				''Opening Agent Transaction'' AS Description,
				oa.invoice_Code TransactionID,
				oa.amount Amount 
				from OpeningAgent oa
				left join Agent a on a.agent_id = oa.agent_Id
				left join Currency cur on cur.CurrencyId = oa.currency_Id
				left join type t on t.type_id = a.type_id
			where oa.branchid = '''+convert(varchar(MAX),@branchid)+''' and(replace(CONVERT(VARCHAR(10),CAST(openingagentDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(cp.TRDate AS DATE),103),''-'','''')Date,
				''Cash Payment Transaction'' AS Description,
				cp.SlipNo TransactionID,
				cp.PaymentAmount Amount
				from CashPayment cp
				left join Agent a on a.agent_id = cp.AgentID
				left join Currency cur on cur.CurrencyId = cp.CurrencyID
				left join type t on t.type_id = a.type_id
			where TRType = 1 and cp.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and (replace(CONVERT(VARCHAR(10),CAST(cp.TRDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(cr.CreatedDate AS DATE),103),''-'','''')Date,
				''CashRemittance To Agent Transaction'' AS Description,
				cr.CRTANo TransactionID,
				cr.TotalPaymentAmount Amount
				from CashRemittanceToAgent cr
				left join Agent a on a.agent_id = cr.ToAgentID
				left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
				left join type t on t.type_id = a.type_id
			WHERE 1 = 1 and (replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''')  between '+@fromdate+' and '+@todate+') and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''''+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE),103),''-'','''')Date,
				''CashRemittance By Agent Transaction'' AS Description,
				cr.ReceiptNo TransactionID,
				((cr.TotalReceivedAmount+cr.extrafees)-cr.Received) Amount
				from CashRemittanceByAgent cr
				left join Agent a on a.agent_id = cr.FromAgentID
				left join Currency cur on cur.CurrencyId = cr.ReceivedCurrencyID
				left join type t on t.type_id = a.type_id
			where TransactionType = 1 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''')  between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			 select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE),103),''-'','''')Date,
				''Cash Remittance By Agent Transaction'' AS Description,
				cr.ReceiptNo TransactionID,
				((cr.TotalReceivedAmount+cr.extrafees)-cr.Received) Amount
				from CashRemittanceByAgent_Archive cr
				left join Agent a on a.agent_id = cr.FromAgentID
				left join Currency cur on cur.CurrencyId = cr.ReceivedCurrencyID
				left join type t on t.type_id = a.type_id
			where cr.FromAgentID <> 0 and cr.TransactionType = 1 and cr.Status = 2 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
			(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(cpr.SlipDate AS DATE),103),''-'','''')Date,
				''Cash Payment By Return Transaction'' AS Description,
				cpr.SlipNo TransactionID,
				cpr.Amount Amount
				from CashPaymentByReturn cpr
				left join Agent a on a.agent_id = cpr.AgentID
				left join Currency cur on cur.CurrencyId = cpr.CurrencyID
				left join type t on t.type_id = a.type_id
			where cpr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and (replace(CONVERT(VARCHAR(10),CAST(cpr.SlipDate AS DATE)),''-'','''')  between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE),103),''-'','''')Date,
				''Agent Cash Transaction'' AS Description,
				act.InvoiceNo TransactionID,
				act.PaidAmount Amount
				from AgentCashTransaction act
				left join Agent a on a.agent_id = act.AgentID
				left join Currency cur on cur.CurrencyId = act.CurrencyID
				left join type t on t.type_id = a.type_id 
			where act.TransactionType = 1 and act.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE)),''-'','''')  between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			 select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(dp.InvoiceDate AS DATE),103),''-'','''')Date,
				''DefinePaymentType Transaction'' AS Description,
				dp.InvoiceNo TransactionID,
				-dptd.Amount Amount
				from DefinePaymentTypeDetail dptd
				left join DefinePaymentType dp on dp.InvoiceID = dptd.InvoiceID
				left join Agent a on a.agent_id = dptd.AgentID
				left join Currency cur on cur.CurrencyId = dptd.CurrencyID
				left join type t on t.type_id = a.type_id
			where isnull(agent_id,0) <> 0 and dp.branchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(dp.InvoiceDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			--union all
			--select
			--	*
			--	from CashReturn cr
			--	left join Agent a on a.agent_id = cr.AgentID
			--	left join Currency cur on cur.CurrencyId = cr.cur
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE),103),''-'','''')Date,
				''Cash Remittance By Agent Transaction'' AS Description,
				cr.ReceiptNo TransactionID,
				-crd.paymentamount Amount
				from CashRemittanceByAgent cr
				left join CashRemittanceByAgentDetails crd on cr.ReceiptID = crd.ReceiptID
				left join Agent a on a.agent_id = crd.ToAgentID
				left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
				left join type t on t.type_id = a.type_id
			where crd.ToAgentID <> 0 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			 select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE),103),''-'','''')Date,
				''Cash Remittance By Agent Transaction'' AS Description,
				cr.ReceiptNo TransactionID,
				-crd.PaymentAmount Amount
				from CashRemittanceByAgent_Archive cr
				left join CashRemittanceByAgentDetails_Archive crd on cr.ReceiptID = crd.ReceiptID
				left join Agent a on a.agent_id = crd.ToAgentID
				left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
				left join type t on t.type_id = a.type_id
			where crd.ToAgentID <> 0  and cr.Status = 2 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
			(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency+
			'union all
			select
				a.agent_code,
				a.name,
				cur.CurrencyCode,
				replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE),103),''-'','''')Date,
				''Agent Cash Transaction'' AS Description,
				act.InvoiceNo TransactionID,
				-act.PaidAmount Amount
				from AgentCashTransaction act
				left join Agent a on a.agent_id = act.AgentID
				left join Currency cur on cur.CurrencyId = act.CurrencyID
				left join type t on t.type_id = a.type_id
			where TransactionType = 0 and act.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
			(replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
			+@colagent+@coltype+@colcurrency
		INSERT INTO @tbl1
		EXEC (@sql)
		--select (@sql)
		--select * from @tbl1
		--select (@sql)
		--declare @table as table
		--(
		--	startdate varchar(8),
		--	enddate varchar(8)
		--)
		--insert into @table
		--	select @startdate,@rdate

		--select * from @table
	end

	else
	begin

			DECLARE @homecurrencyid bigint
			SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)

			set @sqlOpening = 'select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
						select 
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						oa.amount Amount,
						(CASE WHEN cr.currencyratecode IS NULL AND oa.currency_id='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cr.Roper END )as Roper,
						(CASE WHEN cr.currencyratecode IS NULL AND oa.currency_id='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from OpeningAgent oa
						left join Agent a on a.agent_id = oa.agent_Id
						left join Currency cur on cur.CurrencyId = oa.currency_Id
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = oa.currency_Id 
						AND cr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where oa.branchid = '''+convert(varchar(MAX),@branchid)+''' and (replace(CONVERT(VARCHAR(10),CAST(openingagentDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						cp.PaymentAmount Amount,
						(CASE WHEN cr.currencyratecode IS NULL AND cp.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cr.Roper END )as Roper,
						(CASE WHEN cr.currencyratecode IS NULL AND cp.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashPayment cp
						left join Agent a on a.agent_id = cp.AgentID
						left join Currency cur on cur.CurrencyId = cp.CurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = cp.CurrencyID
						AND cr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where TRType = 1 and cp.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and (replace(CONVERT(VARCHAR(10),CAST(cp.TRDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						cr.TotalPaymentAmount Amount,
						cyr.Roper,
						(select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc)as Rate 
						from CashRemittanceToAgent cr
						left join Agent a on a.agent_id = cr.ToAgentID
						left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.PaymentCurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where 1=1 and (replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+') and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''''
					+@colagent+@coltype+')a 
					union all
					select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						((cr.TotalReceivedAmount+cr.extrafees)-Received) Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.ReceivedCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.ReceivedCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashRemittanceByAgent cr
						left join Agent a on a.agent_id = cr.FromAgentID
						left join Currency cur on cur.CurrencyId = cr.ReceivedCurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.ReceivedCurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where TransactionType = 1 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
					+@colagent+@coltype+')a
					union all
					 select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (select
					a.agent_code,
					a.name,
					cur.CurrencyCode,
					((cr.TotalReceivedAmount+cr.extrafees)-cr.Received) Amount,
					(CASE WHEN cyr.currencyratecode IS NULL AND cr.ReceivedCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
					(CASE WHEN cyr.currencyratecode IS NULL AND cr.ReceivedCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
					from CashRemittanceByAgent_Archive cr
					left join Agent a on a.agent_id = cr.FromAgentID
					left join Currency cur on cur.CurrencyId = cr.ReceivedCurrencyID
					left join type t on t.type_id = a.type_id
					LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.ReceivedCurrencyID
					AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
				where cr.FromAgentID <> 0 and Transactiontype = 1  and cr.Status = 2 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
				(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
				+@colagent+@coltype+@colcurrency+')a
				union all
					select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						cpr.Amount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cpr.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cpr.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashPaymentByReturn cpr
						left join Agent a on a.agent_id = cpr.AgentID
						left join Currency cur on cur.CurrencyId = cpr.CurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cpr.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where cpr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and (replace(CONVERT(VARCHAR(10),CAST(cpr.SlipDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					 select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						act.PaidAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND act.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND act.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from AgentCashTransaction act
						left join Agent a on a.agent_id = act.AgentID
						left join Currency cur on cur.CurrencyId = act.CurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = act.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where act.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and act.TransactionType = 1 and 
					(replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						-dptd.Amount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND dptd.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND dptd.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from DefinePaymentTypeDetail dptd
						left join DefinePaymentType dp on dp.InvoiceID = dptd.InvoiceID
						left join Agent a on a.agent_id = dptd.AgentID
						left join Currency cur on cur.CurrencyId = dptd.CurrencyID
						left join type t on t.type_id = a.type_id 
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = dptd.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where isnull(agent_id,0) <> 0 and dp.branchid = '''+convert(varchar(MAX),@branchid)+''' and 
					(replace(CONVERT(VARCHAR(10),CAST(dp.InvoiceDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
					+@colagent+@coltype+')a
					--union all
					--select
					--	*
					--	from CashReturn cr
					--	left join Agent a on a.agent_id = cr.AgentID
					--	left join Currency cur on cur.CurrencyId = cr.cur
					union all
					select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						-crd.PaymentAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashRemittanceByAgent cr
						left join CashRemittanceByAgentDetails crd on cr.ReceiptID = crd.ReceiptID
						left join Agent a on a.agent_id = crd.ToAgentID
						left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.PaymentCurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where crd.ToAgentID <> 0 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and 
					(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
					+@colagent+@coltype+')a
				union all
				 select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (select 
					a.agent_code,
					a.name,
					cur.CurrencyCode,
					-crd.PaymentAmount Amount,
					(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
					(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
					from CashRemittanceByAgent_Archive cr
					left join CashRemittanceByAgentDetails_Archive crd on cr.ReceiptID = crd.ReceiptID
					left join Agent a on a.agent_id = crd.ToAgentID
					left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
					left join type t on t.type_id = a.type_id
					LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.PaymentCurrencyID
					AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
				where crd.ToAgentID <> 0  and cr.Status = 2 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
				(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
				+@colagent+@coltype+@colcurrency+')a
				union all
					select 
						agent_code,
						name,
						currencycode,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						-act.PaidAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND act.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND act.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from AgentCashTransaction act
						left join Agent a on a.agent_id = act.AgentID
						left join Currency cur on cur.CurrencyId = act.CurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = act.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where TransactionType = 0 and act.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE)),''-'','''') between '+@startdate+' and '+@rdate+')'
					+@colagent+@coltype+')a'

						INSERT INTO @tblOpening
						EXEC (@sqlOpening)
						--select (@sqlOpening)

			set @sql = 'select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
						select 
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(oa.openingagentDate AS DATE),103),''-'','''')Date,
						''Opening Agent Transaction'' AS Description,
						oa.invoice_Code TransactionID,
						oa.amount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND oa.currency_Id='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND oa.currency_Id='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from OpeningAgent oa
						left join Agent a on a.agent_id = oa.agent_Id
						left join Currency cur on cur.CurrencyId = oa.currency_Id
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = oa.currency_Id
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where oa.branchid = '''+convert(varchar(MAX),@branchid)+''' and (replace(CONVERT(VARCHAR(10),CAST(openingagentDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(cp.TRDate AS DATE),103),''-'','''')Date,
						''Cash Payment Transaction'' AS Description,
						cp.SlipNo TransactionID,
						cp.PaymentAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cp.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cp.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashPayment cp
						left join Agent a on a.agent_id = cp.AgentID
						left join Currency cur on cur.CurrencyId = cp.CurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cp.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where TRType = 1 and cp.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and (replace(CONVERT(VARCHAR(10),CAST(cp.TRDate AS DATE)),''-'','''')between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(cr.CreatedDate AS DATE),103),''-'','''')Date,
						''CashRemittance To Agent Transaction'' AS Description,
						cr.CRTANo TransactionID,
						cr.TotalPaymentAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashRemittanceToAgent cr
						left join Agent a on a.agent_id = cr.ToAgentID
						left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.PaymentCurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where 1=1 and (replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+') and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''''
					+@colagent+@coltype+')a 
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE),103),''-'','''')Date,
						''CashRemittance By Agent Transaction'' AS Description,
						cr.ReceiptNo TransactionID,
						((cr.TotalReceivedAmount+cr.extrafees)-cr.Received) Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.ReceivedCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.ReceivedCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashRemittanceByAgent cr
						left join Agent a on a.agent_id = cr.FromAgentID
						left join Currency cur on cur.CurrencyId = cr.ReceivedCurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.ReceivedCurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where TransactionType = 1  and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+')a
					union all
						 select 
							agent_code,
							name,
							currencycode,
							Date,
							Description,
							TransactionID,
							(CASE a.Roper
									WHEN ''M'' THEN (a.Rate*a.amount)
									ELSE (a.amount/a.Rate) END )Amount
						 from (select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE),103),''-'','''')Date,
						''CashRemittance By Agent Transaction'' AS Description,
						cr.ReceiptNo TransactionID,
						((cr.TotalReceivedAmount+cr.extrafees)-cr.Received) Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.ReceivedCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
								THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.ReceivedCurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
								THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashRemittanceByAgent_Archive cr
						left join Agent a on a.agent_id = cr.FromAgentID
						left join Currency cur on cur.CurrencyId = cr.ReceivedCurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.ReceivedCurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where cr.FromAgentID <> 0 and Transactiontype = 1  and cr.Status = 2 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+@colcurrency+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(cpr.SlipDate AS DATE),103),''-'','''')Date,
						''Cash Payment By Return Transaction'' AS Description,
						cpr.SlipNo TransactionID,
						cpr.Amount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cpr.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cpr.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashPaymentByReturn cpr
						left join Agent a on a.agent_id = cpr.AgentID
						left join Currency cur on cur.CurrencyId = cpr.CurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cpr.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where (replace(CONVERT(VARCHAR(10),CAST(cpr.SlipDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')and cpr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''''
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					 select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE),103),''-'','''')Date,
						''Agent Cash Transaction'' AS Description,
						act.InvoiceNo TransactionID,
						act.PaidAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND act.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND act.CurrencyID='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from AgentCashTransaction act
						left join Agent a on a.agent_id = act.AgentID
						left join Currency cur on cur.CurrencyId = act.CurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = act.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where act.TransactionType = 1 and act.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(dp.InvoiceDate AS DATE),103),''-'','''')Date,
						''DefinePaymentType Transaction'' AS Description,
						dp.InvoiceNo TransactionID,
						-dptd.Amount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND dptd.CurrencyID ='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND dptd.CurrencyID ='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from DefinePaymentTypeDetail dptd
						left join DefinePaymentType dp on dp.InvoiceID = dptd.InvoiceID
						left join Agent a on a.agent_id = dptd.AgentID
						left join Currency cur on cur.CurrencyId = dptd.CurrencyID
						left join type t on t.type_id = a.type_id 
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = dptd.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where isnull(agent_id,0) <> 0 and dp.branchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(dp.InvoiceDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+')a
					--union all
					--select
					--	*
					--	from CashReturn cr
					--	left join Agent a on a.agent_id = cr.AgentID
					--	left join Currency cur on cur.CurrencyId = cr.cur
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE),103),''-'','''')Date,
						''Cash Remittance By Agent Transaction'' AS Description,
						cr.ReceiptNo TransactionID,
						-crd.PaymentAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID ='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID ='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashRemittanceByAgent cr
						left join CashRemittanceByAgentDetails crd on cr.ReceiptID = crd.ReceiptID
						left join Agent a on a.agent_id = crd.ToAgentID
						left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.PaymentCurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where crd.ToAgentID <> 0 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE),103),''-'','''')Date,
						''Cash Remittance By Agent Transaction'' AS Description,
						cr.ReceiptNo TransactionID,
						-crd.PaymentAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID ='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND cr.PaymentCurrencyID ='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from CashRemittanceByAgent_Archive cr
						left join CashRemittanceByAgentDetails_Archive crd on cr.ReceiptID = crd.ReceiptID
						left join Agent a on a.agent_id = crd.ToAgentID
						left join Currency cur on cur.CurrencyId = cr.PaymentCurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = cr.PaymentCurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where crd.ToAgentID <> 0 and cr.TransactionType = 1 and cr.Status = 2 and cr.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(cr.ReceivedDate AS DATE)),''-'','''') between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+')a
					union all
					select 
						agent_code,
						name,
						currencycode,
						Date,
						Description,
						TransactionID,
						(CASE a.Roper
								WHEN ''M'' THEN (a.Rate*a.amount)
								ELSE (a.amount/a.Rate) END )Amount
					 from (
					select
						a.agent_code,
						a.name,
						cur.CurrencyCode,
						replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE),103),''-'','''')Date,
						''Agent Cash Transaction'' AS Description,
						act.InvoiceNo TransactionID,
						-act.PaidAmount Amount,
						(CASE WHEN cyr.currencyratecode IS NULL AND act.CurrencyID ='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN ''M'' ELSE cyr.Roper END )as Roper,
						(CASE WHEN cyr.currencyratecode IS NULL AND act.CurrencyID ='''+convert(varchar(MAX),@homecurrencyid)+'''
							THEN 1 ELSE (select top(1)rate from CurrencyRateDetail where CurrencyRateId = cyr.CurrencyRateId order by CurrencyRateDetailId desc) END )as Rate
						from AgentCashTransaction act
						left join Agent a on a.agent_id = act.AgentID
						left join Currency cur on cur.CurrencyId = act.CurrencyID
						left join type t on t.type_id = a.type_id
						LEFT JOIN CurrencyRate cyr ON cyr.SourceCurrencyid = act.CurrencyID
						AND cyr.convertcurrencyid = '''+convert(varchar(MAX),@homecurrencyid)+''' and  cyr.branchid = '''+convert(varchar(MAX),@branchid)+'''
					where TransactionType = 0 and act.defaultbranchid = '''+convert(varchar(MAX),@branchid)+''' and
					(replace(CONVERT(VARCHAR(10),CAST(act.InvoiceDate AS DATE)),''-'','''')between '+@fromdate+' and '+@todate+')'
					+@colagent+@coltype+')a'

				INSERT INTO @tbl1
				EXEC (@sql)
				--select (@sql)
	end

	INSERT INTO @tbl2
		SELECT ROW_NUMBER()OVER(PARTITION BY agentID ORDER BY AgentID)No,
			AgentID,
			AgentName,
			Date,
			Description, 
			TransactionID,
			ISNull(Debit,0)Debit,
			ISNull(Credit,0)Credit
		FROM
		(
			SELECT DISTINCT 
				*,
				(CASE  WHEN(amount LIKE '%-%') THEN 'Credit' ELSE 'Debit' END)CD
			FROM @tbl1
		) sub
		PIVOT
		(
			MAX(Amount)FOR CD IN ([Debit],[Credit])
		)AS PivotTable

	--select * from @tbl2

	INSERT INTO @tblAgent
		SELECT ROW_NUMBER() OVER (ORDER BY AgentID )No,AgentID From(SELECT DISTINCT AgentID FROM @tbl1)a

	delete from @tbl1
	
	Declare @openingbalance decimal(38,8)
	DECLARE @closingbalance DECIMAL(38,8)
	set	@openingbalance =0
		
	DECLARE @agentid VARCHAR(100)
	DECLARE  @icount INTEGER
	SET @icount=(SELECT COUNT(1) From @tblAgent)
	DECLARE  @i INTEGER
	SET @i=0
	
	WHILE(@i < @icount)
	BEGIN
		SET @agentid=''
		SELECT @agentid = AgentID FROM @tblAgent WHERE No=@i+1
		
		DELETE FROM @tblAgentInfo
		INSERT INTO @tblAgentInfo
			SELECT ROW_NUMBER() OVER (ORDER BY Date )SrNo,AgentID,AgentName ,Date ,Description,TransactionID,Debit ,Credit FROM @tbl2 WHERE AgentID=@agentid
			
		SET @openingbalance=(SELECT Isnull(SUM(Amount),0) FROM @tblOpening WHERE  AgentID=@agentid  )
		SET	@closingbalance=0
		
		Declare @zbalance decimal(38,8)
		set	@zbalance=0
		
		
		DECLARE @jcount INTEGER
		SET @jcount=(SELECT COUNT(1) FROM @tblAgentInfo)
		DECLARE  @j INTEGER
		SET @j=0
		
		WHILE(@j < @jcount)
		BEGIN
			IF(@j=0)
				BEGIN
					SET @zbalance=(SELECT @OpeningBalance+ISNULL(Debit,0.00)+ISNULL(Credit,0.00) FROM @tblAgentInfo WHERE No=@j+1)
					INSERT INTO @tblResult  SELECT @j+1 No,AgentID,AgentName ,Date ,Description ,TransactionID,Debit ,Credit,@zbalance FROM @tblAgentInfo WHERE No=@j+1  
				END
			ELSE IF(@j>0)
				BEGIN
					SET @zbalance=(SELECT @zbalance+ISNULL(Debit,0.00)+ISNULL(Credit,0.00) FROM @tblAgentInfo WHERE No=@j+1)
					INSERT INTO @tblResult  SELECT @j+1 No,AgentID,AgentName,Date ,Description ,TransactionID,Debit ,Credit,@zbalance FROM @tblAgentInfo WHERE  No=@j+1  
				END
			if(@j+1 =@jcount)
			SET	@closingbalance =@zbalance
			SET @j = @j+1	
		END
		Insert into @tblFinalResult
			select 0 No,@title Title,@agentid ,'' AgentName ,CONVERT(VARCHAR(10),CAST(@fromdate AS DATE),103) Date,'Opening Balance' Description ,'' TransactionID,0 Debit , 0 Credit ,Isnull(@openingbalance ,0)Balance 
			UNION ALL
			select No,@title Title,AgentID,AgentName,Date,Description,TransactionID,Debit,Credit,Balance from @tblResult
			UNION ALL
			select 0 No ,@title Title,@agentid ,'' Agentname ,CONVERT(VARCHAR(10),CAST(@todate AS DATE),103) Date,'Closing Balance' Description ,'' TransactionID,0 Debit , 0 Credit ,Isnull(@closingbalance,0) Balance 
			delete from @tblResult
		SET @i = @i+1
	END

	delete from @tblOpening
	delete from @tblAgentInfo
	delete from @tbl2
	delete from @tblAgent
	delete from @tblResult
	
	select * from @tblFinalResult order by agentID

end


--exec sp_cr_AgentLedger_Rpt
--	@fromdate = '20141010',
--	@todate = '20141012',
--	@fromagentid = N'',
--	@toagentid = N'',
--	@fromtypeid = N'',
--	@totypeid = N'',
--	@currencyid = N'mmk',
--	@checkcur = 1,
--	@branchid = 201409160000008351
GO


